from sqlalchemy import text
from tools.db_utils import DB_CONFIG

def get_course_attendance_rate(conn, course_name):
    """统计课程平均出勤率
    
    Args:
        conn: 数据库连接对象
        course_name: 课程名称
        
    Returns:
        tuple: (课程名称, 总课堂数, 总学生数, 平均出勤率)
    """
    result = conn.execute(text("""
        SELECT 
            cs.course_name,
            COUNT(DISTINCT cs.id) AS total_classes,
            COUNT(DISTINCT ad.student_id) AS total_students,
            ROUND(AVG(
                (SELECT COUNT(DISTINCT ad2.student_id) 
                 FROM attendance_detail ad2 
                 JOIN attendance a2 ON ad2.parent_id = a2.id 
                 WHERE a2.course_schedule_id = cs.id AND ad2.status = '已签到') * 100.0 / 
                (SELECT COUNT(DISTINCT ad3.student_id) 
                 FROM attendance_detail ad3 
                 JOIN attendance a3 ON ad3.parent_id = a3.id 
                 WHERE a3.course_schedule_id = cs.id)
            ), 2) AS avg_attendance_rate
        FROM attendance_detail ad
        JOIN attendance a ON ad.parent_id = a.id
        JOIN course_schedule cs ON a.course_schedule_id = cs.id
        WHERE cs.course_name = :course_name
        GROUP BY cs.course_name
    """), {'course_name': course_name})
    
    return result.fetchone()